﻿//author: Pedro Godinho, nº 6355

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;

// solução obtida pelo colega Sérgio Severino nº 9733
using Devart.Data;
//Devart.Data.dll
//from: http://www.devart.com/dotconnect/sqlite/
using Devart.Data.SQLite;
//Devart.Data.SQLite.dll
//from: http://www.devart.com/dotconnect/sqlite/
using LumenWorks.Framework.IO.Csv;
//LumenWorks.Framework.IO.dll
//from: http://www.codeproject.com/KB/database/CsvReader.aspx


namespace Interface
{
    public class Dados
    {
        /**
        Classe que cria a Base de Dados com o conteudo dos ficheiros CSV's
        fornecidos pelo docente e cria ficheiros CSV's com as estatisticas
        **/
        int CODIGO_DOCENTE = 0;
        int NOME = 1;
        int ULTIMO_GRAU = 2;
        int CURSO = 3;
        int CATEGORIA = 4;
        int REGIME = 5;
        int ANO_LECTIVO = 6;
        int TIPO_ESTABELECIMENTO = 7;
        int NOME_ESTABELECIMENTO = 8;

        public List<string> nomeEstabelecimento;
        public List<string> grau;
        public List<string> ano;
        public List<string> categoria;
        public List<List<string>> lista;
        
        //método para preencher a base de dados, com os dados contidos nos ficheiros csv's
        public void preencher_basedados()
        {
            var lista = new List<string>();
            for (int i = 0; i < 11; i++)
            {
                lista.Add("rebcsv/reb" + i + ".csv");
            }

            // conexão à Base de Dados
            SQLiteConnection db = new SQLiteConnection();
            db.ConnectionString = "Data Source=BaseDados.db;FailIfMissing=False;";
            db.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = db;

            cmd.CommandText = "BEGIN";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "drop table if exists dados";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "create table if not exists dados (CodigoDocente varchar," +
                                                        "Nome varchar," +
                                                        "UltimoGrau varchar," +
                                                        "Curso varchar," +
                                                        "Categoria varchar," +
                                                        "Regime varchar," +
                                                        "AnoLectivo varchar," +
                                                        "TipoEstabelecimento varchar," +
                                                        "NomeEstabelecimento varchar)";

            cmd.ExecuteNonQuery();


            foreach (var file in lista)
            {
                Console.WriteLine(file);
                var csv =
                       new CsvReader(new StreamReader(file), true);

                var headers = csv.GetFieldHeaders();
                // Corre as linhas todas
                while (csv.ReadNextRecord())
                {

                    cmd.CommandText = "INSERT INTO dados (CodigoDocente, " +
                        "Nome, " +
                        "UltimoGrau, " +
                        "Curso, " +
                        "Categoria, " +
                        "Regime, " +
                        "AnoLectivo, " +
                        "TipoEstabelecimento, " +
                        "NomeEstabelecimento) " +
                        "VALUES ('" +
                        csv[CODIGO_DOCENTE].ToString().Replace("'", "ð") + "','" +
                        csv[NOME].ToString().Replace("'", "ð") + "','" +
                        csv[ULTIMO_GRAU].ToString().Replace("'", "ð") + "','" +
                        csv[CURSO].ToString().Replace("'", "ð") + "','" +
                        csv[CATEGORIA].ToString().Replace("'", "ð") + "','" +
                        csv[REGIME].ToString().Replace("'", "ð") + "','" +
                        csv[ANO_LECTIVO].ToString().Replace("'", "ð") + "','" +
                        csv[TIPO_ESTABELECIMENTO].ToString().Replace("'", "ð") + "','" +
                        csv[NOME_ESTABELECIMENTO].ToString().Replace("'", "ð") + "')";
                    cmd.ExecuteNonQuery();
                }
            }

            db.Commit();
            db.Close();
        }

        // Abre a BaseDados
        public void getBaseDados()
        {
            this.preencher_basedados();
            lista = new List<List<string>>();

            ano = new List<string>();
            nomeEstabelecimento = new List<string>();
            grau = new List<string>();
            categoria = new List<string>();


            SQLiteConnection db = new SQLiteConnection();
            db.ConnectionString = "Data Source=BaseDados.db;FailIfMissing=False;";
            db.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = db;

            cmd.CommandText = "SELECT * FROM dados";


            SQLiteDataReader datareader;

            datareader = cmd.ExecuteReader();

            while (datareader.Read())
            {
                lista.Add(new List<string>{datareader[CODIGO_DOCENTE].ToString().Replace("ð", "'"), 
                                           datareader[NOME].ToString().Replace("ð", "'"), 
                                           datareader[ULTIMO_GRAU].ToString().Replace("ð", "'"), 
                                           datareader[CURSO].ToString().Replace("ð", "'"), 
                                           datareader[CATEGORIA].ToString().Replace("ð", "'"), 
                                           datareader[REGIME].ToString().Replace("ð", "'"), 
                                           datareader[ANO_LECTIVO].ToString().Replace("ð", "'"), 
                                           datareader[TIPO_ESTABELECIMENTO].ToString().Replace("ð", "'"), 
                                           datareader[NOME_ESTABELECIMENTO].ToString().Replace("ð", "'")});
            }
            cmd.CommandText = "SELECT distinct AnoLectivo FROM dados";
            datareader = cmd.ExecuteReader();

            while (datareader.Read())
            {
                ano.Add(datareader[CODIGO_DOCENTE].ToString().Replace("ð", "'"));
            }
            ano.Sort();

            cmd.CommandText = "SELECT distinct NomeEstabelecimento FROM dados";
            datareader = cmd.ExecuteReader();

            while (datareader.Read())
            {
                nomeEstabelecimento.Add(datareader[CODIGO_DOCENTE].ToString());
            }
            nomeEstabelecimento.Sort();

            cmd.CommandText = "SELECT distinct UltimoGrau FROM dados";
            datareader = cmd.ExecuteReader();

            while (datareader.Read())
            {
                grau.Add(datareader[CODIGO_DOCENTE].ToString());
            }
            grau.Sort();

            cmd.CommandText = "SELECT distinct Categoria FROM dados";
            datareader = cmd.ExecuteReader();

            while (datareader.Read())
            {
                categoria.Add(datareader[CODIGO_DOCENTE].ToString());
            }
            categoria.Sort();

            db.Close();
        }

        // criação das listas necessárias
        public List<string> getEstabelecimento()
        {
            return nomeEstabelecimento;
        }

        public List<string> getGrau()
        {
            return grau;
        }

        public List<string> getCategoria()
        {
            return categoria;
        }

        /** Este metodo cria um csv com o número de docentes em todos os anos (de 2000 a 2010)
        num dado estabelecimento por categoria.
        O estabelecimento e a categoria são passado comos parametros do método **/
        public void csvNDocentesCategoriaEstabelecimentoAno(string estabelecimento, string categoria)
        {
            string wpath = System.IO.Path.GetDirectoryName(Application.ExecutablePath).ToString();
            string folder = wpath + "//" + "CSV"; //cria uma pasta chamada CSV (NOTA: em Linux deve ser criada manualmente)
            Directory.CreateDirectory(folder);
            var csv = from anos in this.ano
                      select new List<string> {categoria, estabelecimento, anos,(from valor in this.lista
                                                                    where valor[NOME_ESTABELECIMENTO] == estabelecimento && valor[ANO_LECTIVO] == anos && valor[CATEGORIA] == categoria
                                                                    select valor).Count().ToString()};
            string filePath = "CSV/Numero de " + categoria + " em " + estabelecimento + ".csv";
            string delimiter = ",";
            string header = "Categoira," + "Estabelecimento," + "Ano Lectivo," + "Numero de Docentes";

            int length = csv.Count();
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(header); // escreve o cabeçalho do CSV
            foreach (var v in csv)
                
                sb.AppendLine(string.Join(delimiter, v.ToArray()));

            File.WriteAllText(filePath, sb.ToString());
        }

        /** Este metodo cria um csv com o número de docentes em todos os anos (de 2000 a 2010)
        num dado estabelecimento por grau.
        O estabelecimento e o grau são passado comos parametros do método **/
        public void csvNDocentesGrauEstabelecimentoAno(string estabelecimento, string grau)
        {
            string wpath = System.IO.Path.GetDirectoryName(Application.ExecutablePath).ToString();
            string folder = wpath + "//" + "CSV"; //cria uma pasta chamada CSV (NOTA: em Linux deve ser criada manualmente)
            Directory.CreateDirectory(folder);
            var csv = from anos in this.ano
                    select new List<string> {grau, estabelecimento, anos,(from valor in this.lista
                                                                    where valor[NOME_ESTABELECIMENTO] == estabelecimento && valor[ANO_LECTIVO] == anos && valor[ULTIMO_GRAU] == grau
                                                                    select valor).Count().ToString()};
            string filePath = "CSV/Numero de " + grau + " em " + estabelecimento + ".csv";
            string delimiter = ",";
            string header = "Grau," + "Estabelecimento," + "Ano Lectivo," + "Numero de Docentes";

            int length = csv.Count();
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(header);
            foreach (var v in csv)

                sb.AppendLine(string.Join(delimiter, v.ToArray()));

            File.WriteAllText(filePath, sb.ToString());
        }
    }
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
    }
